package com.wendyxie.sproutup.db

import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log
import com.wendyxie.sproutup.entity.TreeEntity
import com.wendyxie.sproutup.entity.UserEntity
import com.wendyxie.sproutup.entity.UserScoreTmplEntity
import com.wendyxie.sproutup.entity.UserTreeEntity
import java.time.LocalDateTime

open class SQLInitHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        const val DATABASE_NAME = "sprout-up.db"
        private const val DATABASE_VERSION = 3 // 已发布到 2
    }

    override fun onCreate(db: SQLiteDatabase) {
        Log.i("SQLInitHelper", "onCreate")
        v1_Schema(db)
        v2_Schema(db)
        v3_Schema(db)
        v4_Schema(db)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {

        if (oldVersion < 2 && newVersion >= 2) {
            v2_Schema(db)
        }
        if (oldVersion < 3 && newVersion >= 3) {
            v3_Schema(db)
        }
        if (oldVersion < 4 && newVersion >= 4) {
            v4_Schema(db)
        }
        if (oldVersion < 5 && newVersion >= 5) {
            v5_Schema(db)
        }
    }

    // V1数据库：创建用户表，并追加用户数据
    private fun v1_Schema(db: SQLiteDatabase) {
        Log.i("SQLInitHelper", "v1_Schema")
        // 创建用户信息表
        SQLUserHelper.createUserTable(db)
        SQLUserHelper.addUser(UserEntity("1", "1", 0, 0, "R.mipmap.tree", "R.mipmap.tree"), db)
        // 创建用户得分记录表
        SQLUserScoreRecordHelper.createUserScoreRecordTable(db)
        // 得分模板表
        SQLUserScoreTmplHelper.createUserScoreTmplTable(db)
        // 设置默认得分模板
        val scoreTmplEntity = UserScoreTmplEntity(10, "DAY", "每日能量", 1)
        scoreTmplEntity.userId = "1"
        scoreTmplEntity.babyId = "1"
        SQLUserScoreTmplHelper.addUserScoreTmpl(scoreTmplEntity, db)

        // 创建树表
        SQLTreeHelper.createTreeTable(db)
        // 追加默认数据
        val tree1 = TreeEntity(1000, "小可爱", "R.mipmap.tree_1")
        tree1.treeId = "1"
        SQLTreeHelper.addTree(tree1, db)
        val tree2 = TreeEntity(5000, "可爱苹果树", "R.mipmap.tree_2")
        tree2.treeId = "2"
        SQLTreeHelper.addTree(tree2, db)
        val tree3 = TreeEntity(10000, "花树", "R.mipmap.tree_3")
        tree3.treeId = "3"
        SQLTreeHelper.addTree(tree3, db)
        val tree4 = TreeEntity(20000, "花树2", "R.mipmap.tree_4")
        tree4.treeId = "4"
        SQLTreeHelper.addTree(tree4, db)
        val tree5 = TreeEntity(5000, "桂花树", "R.mipmap.tree_5")
        tree5.treeId = "5"
        SQLTreeHelper.addTree(tree5, db)
        val tree6 = TreeEntity(1000, "梅花树_卡通", "R.mipmap.tree_6")
        tree6.treeId = "6"
        SQLTreeHelper.addTree(tree6, db)
        val tree7 = TreeEntity(50000, "桂花树_卡通", "R.mipmap.tree_7")
        tree7.treeId = "7"
        SQLTreeHelper.addTree(tree7, db)
        var tree8 = TreeEntity(1000, "梅花树", "R.mipmap.tree_8")
        tree8.treeId = "8"
        SQLTreeHelper.addTree(tree8, db)

        // 创建用户购买记录表
        SQLUserTreeHelper.createUserTreeTable(db)
    }

    // V2数据库：创建树表，并追加树表数据
    private fun v2_Schema(db: SQLiteDatabase) {
        Log.i("SQLInitHelper", "v2_Schema")
        var tree9 = TreeEntity(2000, "水晶树", "R.mipmap.tree_9")
        tree9.treeId = "9"
        SQLTreeHelper.addTree(tree9, db)

        var tree0 = TreeEntity(0, "默认", "R.mipmap.tree")
        tree0.treeId = "0"
        SQLTreeHelper.addTree(tree0, db)

        // 默认用户已购买
        val userTreeEntity = UserTreeEntity("1", "1", tree0.treeId, tree0.treePrice, tree0.treeName, tree0.treeImageUrl)
        SQLUserTreeHelper.addUserTree(userTreeEntity, db)
        // 默认选中状态
        val userEntity = SQLUserHelper.queryBabyById("1", db)
        userEntity.selectedTreeId = tree0.treeId
        userEntity.selectedTreeSkinUrl = tree0.treeImageUrl
        SQLUserHelper.updateUserById(userEntity, db)
    }

    private fun v3_Schema(db: SQLiteDatabase) {
        Log.i("SQLInitHelper", "v3_Schema")
        // 新增树
        var tree10 = TreeEntity(100, "毛绒福树", "R.mipmap.tree_10")
        tree10.treeId = "10"
        SQLTreeHelper.addTree(tree10, db)

        // 新增用户密码字段
        SQLUserHelper.addBabyPwdColumn(db)
        Log.i("SQLInitHelper", "v3_Schema end")
    }

    private fun v4_Schema(db: SQLiteDatabase) {

    }

    private fun v5_Schema(db: SQLiteDatabase) {

    }

    fun openDatabase(): SQLiteDatabase {
        return this.writableDatabase
    }

    fun closeDatabase() {
        if (this.writableDatabase.isOpen) {
            this.writableDatabase.close()
        }
        if (this.readableDatabase.isOpen) {
            this.readableDatabase.close()
        }
    }

    fun <T> queryData(query: String, selectionArgs: Array<String>? = null, mapper: (Cursor) -> T): List<T> {
        val resultList = mutableListOf<T>()
        val db = openDatabase()
        val cursor = db.rawQuery(query, selectionArgs)

        cursor.use {
            while (it.moveToNext()) {
                resultList.add(mapper(it))
            }
        }
        cursor.close()
        db.close()
        return resultList
    }

}
